//使用Express框架搭建服务器
const express = require('express');
//引入mysql模块
const mysql1 = require('mysql');
// 跨越访问
const cors = require('cors');
//引入body-parser模块
const bodyParser = require('body-parser');
//使用body-parser中间件
const app = express();

// 跨域访问 - 确保CORS中间件最先执行
app.use(cors());

// 请求体解析中间件
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

//创建连接池
const pool = mysql1.createPool({
    host: 'localhost',
    user: 'root',   
    password: '123456',
    database: 'servedb'
});

pool.on('connection', function (connection) {
    console.log('Connection Id: ' + connection.threadId);
});

// 获取学生信息的接口,带分页page,pageSize
app.get('/api/getStudent', (req, res) => {
   var {page,pageSize} = req.query;
   console.log(page,pageSize);
    pool.query('SELECT * FROM user where user_permission=2 order by user_id asc limit ?,? ', [(page-1)*pageSize,parseInt(pageSize)], (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error getting student');
        } else {
            res.json(result);
        }
    });
});

// 获取学生总数的接口
app.get('/api/getStudentCount', (req, res) => {
    pool.query('SELECT COUNT(*) as count FROM user where user_permission=2', (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error getting student count');
        } else {
            res.json(result);
        }
    });
});

// 添加学生信息的接口
app.post('/api/addStudent', (req, res) => {
    var {user_id,user_name,user_adress,user_password,user_price} = req.body;
    var user_creatime = new Date();
    pool.query('INSERT INTO user (user_id,user_name,user_adress,user_password,user_price,user_creatime) VALUES (?,?,?,?,?,NOW())', [user_id,user_name,user_adress,user_password,user_price,user_creatime], (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error adding student');
        } else {
            res.json(result);
        }
    });
});

// 修改学生信息的接口
app.post('/api/updateStudent', (req, res) => {
    var {user_id,user_name,user_adress,user_password,user_price} = req.body;
    var user_creatime = new Date();
    pool.query('UPDATE user SET user_name=?,user_adress=?,user_password=?,user_price=?,user_creatime=? WHERE user_id=?', [user_name,user_adress,user_password,user_price,user_creatime,user_id], (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error updating student');
        } else {
            res.json(result);
        }
    });
});

// 删除学生信息的接口
app.post('/api/deleteStudent', (req, res) => {
    var {user_id} = req.body;
    pool.query('DELETE FROM user WHERE user_id=?', [user_id], (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error deleting student');
        } else {
            res.json(result);
        }
    });
});

//用户登录接口
app.post('/api/login', (req, res) => {
    var {sid,password} = req.body;
    pool.query('SELECT * FROM user WHERE user_account=? AND user_password=? and user_permission=3', [sid,password], (err, result) => {
        if (err) {
            console.log(err);
            return res.status(500).json({code:500,msg:'服务器错误',data:err});
        }
        
        if(result.length > 0){
            return res.json({code:200,msg:'登录成功',data:result});
        }
        
        return res.json({code:400,msg:'登录失败',data:result});
    });
});

// 用户注册接口
app.post('/api/register', (req, res) => {
    var {sid,password,role} = req.body;
    pool.query('INSERT INTO user (user_account,user_password,user_permission) VALUES (?,md5(?),?)', [sid,password,role], (err, result) => {
        if (err) {
            console.log(err);
            res.json({code:400,msg:'注册失败',data:err});
        } else {            
            res.json({code:200,msg:'注册成功',data:result});
        }
    });
});

//检查用户是否存在接口
app.post('/api/check', (req, res) => {
    var {sid} = req.body;
    pool.query('SELECT * FROM user WHERE user_id=?', [sid], (err, result) => {
        if (err) {
            console.log(err);
            res.json({code:400,msg:'检查用户是否存在失败',data:err});
        } else {
            if(result.length > 0){
                res.json({code:200,msg:'用户已存在',data:result});
            }else{
                res.json({code:400,msg:'用户不存在',data:result});
            }
        }
    });
});

//监听端口
app.listen(3000, () => {
    console.log('Server is running on port 3000');
});
